package com.kim.bookshop.book.daoimpl;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.kim.bookshop.book.bean.Book;
import com.kim.bookshop.book.bean.PageBean;
import com.kim.bookshop.book.dao.BookDao;
import com.kim.bookshop.category.bean.Category;

import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;

public class BookDaoImpl implements BookDao{
	
	private QueryRunner qr=new TxQueryRunner();
	
	@Override
	public void add(Book book) {
		Date utildate=book.getPostdate();
		java.sql.Date   sqldate=new java.sql.Date(utildate.getTime());  
		String sql="insert into book(bid,bname,price,author,image,cid,publish,isbn,stock,sumary,postdate) values(?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params={
				book.getBid(),
				book.getBname(),
				book.getPrice(),
				book.getAuthor(),
				book.getImage(),
				book.getCategory().getCid(),
				book.getPublish(),
				book.getIsbn(),
				book.getStock(),
				book.getSumary(),
				sqldate
		};
		try {
			qr.update(sql,params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void delete(String bid) {
		String sql="update book set del=true where bid=?";
		try {
			qr.update(sql,bid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

	@Override
	public void edit(Book book) {
		String sql="update book set bname=?,price=?,author=?,cid=?,isbn=?,publish=?,stock=?,sumary=? where bid=?";
		Object[] params={book.getBname(),
										  book.getPrice(),
										  book.getAuthor(),
										  book.getCategory().getCid(),
										  book.getIsbn(),
										  book.getPublish(),
										  book.getStock(),
										  book.getSumary(),
										  book.getBid()};
		try {
			qr.update(sql,params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Book> findAll() {
		String sql="select * from book where del=false";
		try {
			return qr.query(sql, new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Book> findByCategory(String cid) {
	//	String sq="select * from book where cid=? and del=false";
		try {
			StringBuilder sql=new StringBuilder("select * from book where  del=false ");
			if(cid!=null){
				sql.append("and cid=?");
				return qr.query(sql.toString(), new BeanListHandler<Book>(Book.class),cid);
			}
			return qr.query(sql.toString(), new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	/**
	 * 书籍当中包含分类对象，
	 * 需分别封装成对象，设置两者关系
	 */
	public Book findByBid(String bid) {
		try{
			String sql="select * from book where bid=?";
			Map<String,Object> map=qr.query(sql,new MapHandler(), bid);
			Category category=CommonUtils.toBean(map, Category.class);
			Book book=CommonUtils.toBean(map, Book.class);
			book.setCategory(category);
			return book;
		}catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public int getCountByCid(String cid) {
		String sql="select count(*) from book where cid=?";
		try {
			Number count=(Number) qr.query(sql, new ScalarHandler(),cid);
			return count.intValue();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Book> getStack() {
		String sql="select bname,stock  from book";
		try {
				return  qr.query(sql, new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void updateStock(String bid,int count) {
		try {
				String sql="update book set " +
				"stock=?  where bid=?";
				Object[] params={
				count,bid
				};
			qr.update(sql,params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

	@Override
	public int getStockByBid(String bid) {
		try {
			String sql = "select stock from book where bid=?";
			return (Integer)qr.query(sql, new ScalarHandler(), bid);
		} catch(SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void cleanStock(String bid) {
		String sql="update book set stock=0  where bid=?";
		try {
			qr.update(sql,bid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		
	}

	//获取最新书籍
	@Override
	public List<Book> getHotBooks(Date date) {
		String sql="select * from book where del=false and postdate<? limit 10";
		try {
			java.sql.Date sqlDate=new java.sql.Date(date.getTime());
			return qr.query(sql, new BeanListHandler<Book>(Book.class),sql);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	//获取畅销书籍
	@Override
	public List<Book> getPopBooks() {
		String sql="SELECT * FROM book  b,(SELECT * FROM orderitem )  t1 "
				+ "WHERE b.bid=t1.bid "
				+ "ORDER BY  t1.`count` DESC LIMIT 5";
		try {
			return qr.query(sql, new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public List<Book> getOffLine() {
		String sql="select * from book where del=true";
		try {
			return qr.query(sql, new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void reonline(String bid) {
		String sql="update book set del=false where bid=?";
		try {
			qr.update(sql,bid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

	@Override
	public PageBean<Book>  searchBook(String bname) {
		try{
			PageBean<Book> pBean=new PageBean<Book>();
			String sql="select * from book where bname like ?";
			List<Book> beanList=qr.query(sql, new BeanListHandler<Book>(Book.class),"%"+bname+"%");
			pBean.setBeanList(beanList);
			return  pBean;
		}catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	
	@Override
	public PageBean<Book> findAllPage(int pc, int ps) {
		try {
			PageBean<Book> pb=new PageBean<Book>();
			pb.setPc(pc);
			pb.setPs(ps);
		
			String sqlcount="select count(*) from book";
			Number num=(Number) qr.query(sqlcount, new ScalarHandler());
			pb.setTr(num.intValue());
			
			String sql="select * from book where del=false limit ?,?";
			List<Book> beanList=	 qr.query(sql, new BeanListHandler<Book>(Book.class),(pc-1)*10,ps);
			pb.setBeanList(beanList);
			return pb;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public PageBean<Book> findByCategoryPage(String cid,int pc,int ps) {
		try {
			PageBean<Book> pb=new PageBean<Book>();
			pb.setPc(pc);
			pb.setPs(ps);
		
			String sqlcount="select count(*) from book";
			Number num=(Number) qr.query(sqlcount, new ScalarHandler());
			pb.setTr(num.intValue());
			
			StringBuilder sql=new StringBuilder("select * from book where  del=false  limit ?,?");
			if(cid!=null){
				sql.append("and cid=?");
				List<Book> beanList=qr.query(sql.toString(),new BeanListHandler<Book>(Book.class),(pc-1)*10,ps,cid);
				pb.setBeanList(beanList);
				return pb;
			}
				List<Book> beanList=	 qr.query(sql.toString(), new BeanListHandler<Book>(Book.class),(pc-1)*10,ps);
				pb.setBeanList(beanList);
				return pb;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	

	

	

}
